package Controller;

import Model.BangDiem;
import Model.KhoaHoc;
import Model.LopHoc;
import Model.MonHoc;
import Model.SinhVien;
import Model.Users;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 
 * @author Nhóm java lớp PNV2T - Nguyễn Thị Lệ Đài - Nguyễn Chí Hướng
 * 
 *         Contain all search and validation function of application
 */
public class Search {
	private ConnectDB con = null;
	private ResultSet rs = null;
	private Vector vec = null;

	/** Creates a new instance of Search */
	public Search() {
		try {
			con = new ConnectDB();
			con.open();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Check login
	 * 
	 * @param Username
	 * @param Password
	 * @return Check account is exist
	 */
	public boolean isLogin(String Username, String Password) {
		String sql = "Select * From Users Where TaiKhoan='" + Username
				+ "' and MatKhau='" + Password + "'";
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @return vector of all course
	 */
	public Vector DanhSachKhoaHoc() {
		String sql = "Select * From KhoaHoc Order By (TenKhoa)";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				KhoaHoc KH = new KhoaHoc();
				KH.setID(rs.getInt(1));
				KH.setTenKhoa(rs.getString(2));
				KH.setBatDau(rs.getString(3));
				vec.add(KH);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * Get list user
	 * 
	 * @return
	 */
	public Vector DanhSachNguoiDung() {
		String sql = "Select * From Users Order By (TaiKhoan)";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				Users user = new Users();
				user.setId(rs.getInt(1));
				user.setTaiKhoan(rs.getString(2));
				user.setMatKhau(rs.getString(3));
				user.setTenXacNhan(rs.getString(4));
				vec.add(user);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param type
	 *            : type of searching are: - KhoaHoc - HeDaoTao
	 * @return vector classes
	 */
	public Vector DanhSachLopHoc(int type, int khoa) {
		String sql = "";
		if (type == 1)
			sql = "Select *,TenKhoa From LopHoc join KhoaHoc On LopHoc.KhoaHoc = KhoaHoc.ID Order by (KhoaHoc)";
		else if (type == 2)
			sql = "Select *,TenKhoa From LopHoc join KhoaHoc On LopHoc.KhoaHoc=KhoaHoc.ID Order by (HeDaoTao)";
		else if (type == 3)
			sql = "Select *,TenKhoa From LopHoc join KhoaHoc On LopHoc.KhoaHoc=KhoaHoc.ID where KhoaHoc = '"+khoa+"'";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			System.out.println(sql);
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				LopHoc lop = new LopHoc();
				lop.setID(rs.getInt(1));
				lop.setTenLop(rs.getString(2));
				lop.setMaKhoa(rs.getInt(3));
				lop.setHeDaoTao(rs.getString(4));
				lop.setChuNhiem(rs.getString(5));
				lop.setLopTruong(rs.getString(6));
				lop.setKhoaHoc(rs.getString(7));
				vec.add(lop);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param type
	 * @param MaKhoa
	 * @param KeyName
	 * @return
	 */
	public Vector DanhSachLopHocChiTiet(int type, int MaKhoa, String KeyName) {
		String sql = "";
		if (type == 1)
			sql = "Select * From LopHoc Where KhoaHoc=" + MaKhoa
					+ "Order by (ID)";
		else if (type == 2)
			sql = "Select * From LopHoc Where KhoaHoc=" + MaKhoa
					+ "and TenLop like '%" + KeyName + "%'";
		else if (type == 3)
			sql = "Select * From LopHoc Where KhoaHoc=" + MaKhoa
					+ "and ChuNhiem like N'%" + KeyName + "%'";
		else if (type == 4)
			sql = "Select * From LopHoc Where KhoaHoc=" + MaKhoa
					+ "and LopTruong like N'%" + KeyName + "%'";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				LopHoc lop = new LopHoc();
				lop.setID(rs.getInt(1));
				lop.setTenLop(rs.getString(2));
				lop.setMaKhoa(rs.getInt(3));
				lop.setHeDaoTao(rs.getString(4));
				lop.setChuNhiem(rs.getString(5));
				lop.setLopTruong(rs.getString(6));
				vec.add(lop);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param type
	 * @param MaLop
	 * @param KeyName
	 * @return Vector of all student
	 */
	public Vector DanhSachSinhVien(int type, int MaLop, String KeyName) {
		String sql = "";
		if (type == 1) {
			sql = "Select * From SinhVien Where MaLop=" + MaLop
					+ " Order by (MaSV)";
			System.out.println(sql);
		} else if (type == 2) {
			sql = "Select * From SinhVien Where MaLop = " + MaLop
					+ "and MaSV like '%" + KeyName.trim()
					+ "%' Order by (MaSV)";
			System.out.println(sql);
		} else if (type == 3) {
			sql = "Select * From SinhVien Where MaLop = " + MaLop
					+ "and Ten like N'%" + KeyName.trim()
					+ "%' Order by (MaSV)";
			System.out.println(sql);
		} else if (type == 4) {
			sql = "Select * From SinhVien Where MaSV = '" + KeyName.trim()
					+ "' Order by (MaSV)";
			System.out.println(sql);
		}
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				SinhVien sv = new SinhVien();
				sv.setID(rs.getInt("ID"));
				sv.setMaSV(rs.getString("MaSV"));
				sv.setMaLop(rs.getInt("MaLop"));
				sv.setTen(rs.getString("Ten"));
				sv.setQueQuan(rs.getString("QueQuan"));
				sv.setNgaySinh(rs.getString("NgaySinh"));
				sv.setGioiTinh(rs.getString("GioiTinh"));
				sv.setTonGiao(rs.getString("TonGiao"));
				vec.add(sv);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param MaSV
	 * @return Student has MaSV like parameter was passed
	 */
	public SinhVien getSinhVien(String MaSV) {
		String sql = "Select * From SinhVien Where MaSV ='" + MaSV + "'";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		SinhVien sv = new SinhVien();
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				sv.setID(rs.getInt(1));
				sv.setMaSV(rs.getString(2));
				sv.setMaLop(rs.getInt(3));
				sv.setTen(rs.getString(4));
				sv.setQueQuan(rs.getString(5));
				sv.setNgaySinh(rs.getString(6));
				sv.setGioiTinh(rs.getString(7));
				sv.setTonGiao(rs.getString(8));
				vec.add(sv);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return sv;
	}

	/**
	 * @return The last student's code in database
	 */
	public String MaSVGanNhat() {
		String sql = "Select Top 1 MaSV From SinhVien Order by (MaSV) DESC";
		con.setMode(ConnectDB.MODE_NORMAL);
		String MaSV = "";
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				MaSV = rs.getString(1);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return MaSV;
	}

	/**
	 * @param MaSV
	 * @return Check if student's code has in database
	 */
	public boolean KiemTraMaSV(String MaSV) {
		String sql = "Select MaSV From SinhVien Where MaSV = '" + MaSV + "'";
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @return vector of all subject
	 */
	public Vector DanhSachMonHoc() {
		String sql = "Select * From MonHoc Order by (TenMon)";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				MonHoc MH = new MonHoc();
				MH.setID(rs.getInt(1));
				MH.setTenMon(rs.getString(2));
				MH.setKyHoc(rs.getInt(3));
				MH.setNamHoc(rs.getInt(4));
				MH.setSoTinChi(rs.getInt(5));
				vec.add(MH);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param MaSV
	 * @return Table of students' grades
	 */
	public Vector BangDiemSinhVien(int MaSV) {
		String sql = "Select b.MaSV,Ten,b.MaMon,TenMon,DiemKiemTra,DiemChuyenCan,DiemThi,DiemTongKet From SinhVien as a,KetQuaHocTap as b,MonHoc as c Where a.ID = b.MaSV and c.ID=b.MaMon and b.MaSV="
				+ MaSV + " Order By (TenMon)";
		System.out.println(sql);
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				BangDiem diem = new BangDiem();
				diem.setMaSV(rs.getInt(1));
				diem.setTenSinhVien(rs.getString(2));
				diem.setMaMon(rs.getInt(3));
				diem.setTenMon(rs.getString(4));
				diem.setDiemKiemTra(rs.getDouble(5));
				diem.setDiemChuyenCan(rs.getDouble(6));
				diem.setDiemThi(rs.getDouble(7));
				diem.setDiemTongKet(rs.getDouble(8));
				vec.add(diem);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

	/**
	 * @param MaSV
	 * @param MaMon
	 * @return Check result of studying by MaSV and MaMon
	 */
	public boolean KiemTraBangDiem(int MaSV, int MaMon) {
		String sql = "Select * From KetQuaHocTap Where MaSV =" + MaSV
				+ " and MaMon = " + MaMon;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @param MaMon
	 * @return Check if Subject is exist
	 */
	public boolean KiemTraMon(int MaMon) {
		String sql = "Select * From KetQuaHocTap Where MaMon = " + MaMon;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @param MaSV
	 * @return Check if student has result of studying
	 */
	public boolean KiemTraSVBangDiem(int MaSV) {
		String sql = "Select * From KetQuaHocTap Where MaSV = " + MaSV;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @param MaLop
	 * @return Check if class is exist
	 */
	public boolean KiemTraLopHoc(int MaLop) {
		String sql = "Select * From SinhVien Where MaLop = " + MaLop;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @param MaKhoa
	 * @return Check if course has any class
	 */
	public boolean KiemTraKhoaHoc(int MaKhoa) {
		String sql = "Select * From LopHoc Where KhoaHoc = " + MaKhoa;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * @param ID
	 * @return Class has ID was passed
	 */
	public LopHoc getLop(int ID) {
		String sql = "Select * From LopHoc Where ID = " + ID;
		con.setMode(ConnectDB.MODE_NORMAL);
		LopHoc lop = new LopHoc();
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				lop.setID(rs.getInt(1));
				lop.setTenLop(rs.getString(2));
				lop.setMaKhoa(rs.getInt(3));
				lop.setHeDaoTao(rs.getString(4));
				lop.setChuNhiem(rs.getString(5));
				lop.setLopTruong(rs.getString(6));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return lop;
	}

	public int getCourseId(String courseName) {
		System.out.println();
		int courseId = 0;
		String sql = "Select * From KhoaHoc Where TenKhoa = '" + courseName
				+ "'";
		System.out.println(sql);
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			if (rs.next()) {
				courseId = rs.getInt(1);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return courseId;
	}

	/**
	 * Close connection
	 */
	public void CloseData() {
		try {
			con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public String XacNhanTaiKhoan(String taiKhoan, String tenXacNhan) {
		String sql = "Select * From Users Where TaiKhoan = '" + taiKhoan
				+ "' and TenXacNhan = '" + tenXacNhan + "'";
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				return ("Mật khẩu của bạn là " + rs.getString("MatKhau"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return "Thông tin sai.";
	}

	public Users getUser(String userName, String passWord) {
		String sql = "Select * From Users Where TaiKhoan = '" + userName
				+ "' and MatKhau = '" + passWord + "'";
		Users user = null;
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			user = new Users();
			while (rs.next()) {
				user.setTaiKhoan(rs.getString("TaiKhoan"));
				user.setMatKhau(rs.getString("MatKhau"));
				user.setId(rs.getInt("ID"));
				user.setTenXacNhan(rs.getString("TenXacNhan"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return user;
	}

	public Vector getListStudentOfClass(String maLop, int types) {
		String sql = "";
		if (types == 1)
			sql = "Select * From SinhVien Order by (MaLop)";
		else if (types == 2)
			sql = "Select * From SinhVien Where MaLop = " + maLop
					+ "Order by (MaLop)";
		vec = new Vector();
		con.setMode(ConnectDB.MODE_NORMAL);
		try {
			rs = con.doQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		try {
			while (rs.next()) {
				SinhVien sv = new SinhVien();
				sv.setID(rs.getInt(1));
				sv.setMaSV(rs.getString(2));
				sv.setMaLop(rs.getInt(3));
				sv.setTen(rs.getString(4));
				sv.setQueQuan(rs.getString(5));
				sv.setNgaySinh(rs.getString(6));
				sv.setGioiTinh(rs.getString(7));
				sv.setTonGiao(rs.getString(8));
				vec.add(sv);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return vec;
	}

}
